# Database, profiles, and storage

> Supacharger keeps internal data in the unexposed `app` schema. Browser-callable RPCs live in `api`; trusted service-only Stripe, webhook, and maintenance RPCs live in `api_edge`. New application tables should follow the same boundary and enable RLS as defence in depth.

# Database, profiles, and storage

Supacharger keeps internal data in the unexposed `app` schema. Browser-callable RPCs live in `api`; trusted service-only Stripe, webhook, and maintenance RPCs live in `api_edge`. New application tables should follow the same boundary and enable RLS as defence in depth.

## Canonical profile

Every Supabase Auth user has one `app.profiles` row keyed by the Auth UUID. Auth insert and email-update triggers maintain it automatically. A forward repair migration fills rows missing from installations that created Auth users before those triggers existed without overwriting existing profiles. Repaired usernames remain null so the application's configured identity and onboarding policy remains authoritative. Shared fields are `first_name`, `last_name`, `username`, `email`, `avatar_path`, `header_image_path`, `language_code`, `created_at`, and `updated_at`.

Applications may add product-specific profile fields or a one-to-one extension. Do not create another shared user table. Username is nullable and case-insensitively unique when populated. `PROFILE_IDENTITY.USERNAME` decides whether it is disabled, optional, or required; it never adds or removes the database column.

Completion always requires non-empty first and last name and requires username only when the caller passes `input_username_required: true`. `POST_SIGN_IN_ONBOARDING` decides whether incomplete signed-in users are redirected; it does not control row creation. Identity display falls back from names, to an enabled populated username, to owner-only email, to `Account`. Email never enters public projections.

The current-user profile endpoint may return the owner's email. General identity lookup uses `api.get_user_profiles_by_identifiers()` and deliberately returns a safe projection without email or unrestricted profile data.

## Private profile media

The `user-avatars` bucket is private. Store object paths rather than public URLs:

- `<user-id>/avatar-<unique-name>` for avatars;
- `<user-id>/headers/header-<unique-name>` for header images.

Uploads accept JPEG, PNG, and WebP files up to 5 MB. Owners control writes within their own prefix. Render profile images with short-lived signed URLs; do not turn the bucket public or persist signed URLs in `app.profiles`.

## Account summary for application chrome

Use the managed account-summary boundary for headers, sidebars, and account menus. `api.get_account_summary_by_current_user()` reads the signed-in profile, active organisation context, membership role, and organisation count in one database query. The server-only `loadAccountSummary()` service verifies the user, validates that response, derives the display name and initials, and signs only the private media the current summary needs. `GET /api/account/summary` exposes the same safe result with private no-store caching.

Menus keep their product-specific visual design but should read through `useAccountSummary()`, not query the profile RPC or Storage directly. After a profile or avatar mutation succeeds, call `publishAccountSummaryChanged()`. Mounted menus then refresh through the shared `supacharger:account-summary-changed` event. The shared signed-media resolver batches paths by bucket with one `createSignedUrls()` call per bucket, avoiding one signing request per image.

## Optional organisation data

Every aligned schema installs private organisation, membership, invitation, access-request, and session-context tables. They remain unused when `ORGANISATIONS.ENABLED` is false. Browser roles have no direct table access. Authenticated application code calls `api.organisations(input_payload)`; the dispatcher derives the user, verified email, and Auth `session_id` from the JWT. The Auth hook can project the active organisation into refreshed claims. RLS must still verify membership at the data boundary.

The private `organisation-logos` bucket stores organisation avatars and headers. It accepts JPEG, PNG, WebP, and GIF objects up to 5 MB. Store paths under the organisation UUID and persist paths rather than signed URLs. Members can read media for their organisations; owners and admins control writes.

See [Organisation management](./organisations.md) for actions, role rules, invitations, access review, session refresh, and the Specdrive compatibility boundary.

## Applying and checking migrations

Create changes with `supabase migration new`, apply them to a clean local database, run `supabase db lint --local`, then regenerate TypeScript types for `public,api,api_edge,app`. Exposed RPC argument names must not use the historical `p_` prefix. Prefer descriptive names; use `input_` where a parameter could collide with a table column or output field, and `result_limit` for limits. Qualify columns and variables in SQL rather than relying on a prefix. A profile schema change is incomplete until the Proxy and server-access boundary, callbacks, forms, uploads, RPC documentation, Bruno requests, and generated types all use the same field names.